﻿-- =============================================
-- Author:		Taylor Chase
-- Create date: May 19 2016
-- Description:	
-- =============================================
CREATE FUNCTION [dbo].[DataConversion_ParentStaffRelationships] 
(

)
RETURNS 
@JSONtoWrite TABLE (JSONvalue nvarchar(MAX),EntityID int,JSONvalue2 nvarchar(MAX),WriteType nvarchar(100))
AS
BEGIN

	
	--PRINT 'Update Parent Relationships'
	-- Create or update Father/Mother Relationships
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID,WriteType)
	SELECT '{"ShortRelationshipName":"Father"' 
		 + ',"Entity1ID":"' + CAST(RP.EntityID AS NVARCHAR) + '"'
		 + ',"Entity2ID":"' + CAST(RS.EntityID AS NVARCHAR) + '"}' AS JSONvalue,RP.EntityID AS EntityID,'EntityRelationship' AS WriteType
	--SELECT * 
	FROM 
	OPENQUERY(STUDDATA97,'SELECT StudentID,FatherID FROM Students') S
	INNER JOIN  OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {Person}''') AS RS
			ON RS.PersonID = S.StudentID
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {Person}''') AS RP
			ON RP.PersonID = CAST(FatherID + 10000 AS NVARCHAR)
	LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {Father}''') AS F
			ON F.Entity1ID = RP.EntityID AND F.Entity2ID = RS.EntityID
	WHERE F.ShortRelationshipName IS NULL

	INSERT INTO @JSONtoWrite(JSONvalue,EntityID,WriteType)
	SELECT '{"ShortRelationshipName":"Mother"' 
		 + ',"Entity1ID":"' + CAST(RP.EntityID AS NVARCHAR) + '"'
		 + ',"Entity2ID":"' + CAST(RS.EntityID AS NVARCHAR) + '"}' AS JSONvalue,RP.EntityID AS EntityID,'EntityRelationship' AS WriteType
	--SELECT * 
	FROM 
	OPENQUERY(STUDDATA97,'SELECT StudentID,MotherID FROM Students') S
	INNER JOIN  OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {Person}''') AS RS
			ON RS.PersonID = S.StudentID
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {Person}''') AS RP
			ON RP.PersonID = CAST(MotherID + 10000 AS NVARCHAR)
	LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {Mother}''') AS F
			ON F.Entity1ID = RP.EntityID AND F.Entity2ID = RS.EntityID
	WHERE F.ShortRelationshipName IS NULL	

	--PRINT 'Remove Generic Parent Relationships.'	
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID,WriteType)
	SELECT '{"RequestedAction":"Delete","DeleteOverride":"True",'
		 + '"ShortRelationshipName":"Parent"' 
		 + ',"Entity1ID":"' + CAST(Par.Entity1ID AS NVARCHAR) + '"'
		 + ',"Entity2ID":"' + CAST(FAM.Entity2ID AS NVARCHAR) + '"}' AS JSONvalue,FAM.Entity1ID AS EntityID,'EntityRelationship' AS WriteType
	--SELECT * 
	FROM 
	OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {Parent} WHERE ShortRelationshipName <> "Father" AND ShortRelationshipName <> "Mother"''') AS Par
	LEFT JOIN 
	OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''(SELECT * FROM {Father}) UNION (SELECT * FROM {Mother})''') AS FAM
		ON	Par.Entity1ID = FAM.Entity1ID AND Par.Entity2ID = FAM.Entity2ID
	WHERE NOT FAM.ShortRelationshipName IS NULL

	--PRINT 'Update Staff Relationships'
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID,WriteType)
	SELECT '{"ShortRelationshipName":"Staff"' 
		 + ',"Entity1ID":"' + CAST(P.EntityID AS NVARCHAR) + '"'
		 + ',"Entity2ID":"10000"'
		 + ',"StartDate":"'+CAST(CYT.StartDate AS NVARCHAR)+'"' + ',"EndDate":"'+CAST(CYT.EndDate AS NVARCHAR)+'"}' AS JSONvalue,
		 P.EntityID AS EntityID,'EntityRelationship' AS WriteType
	--SELECT * 
	FROM 
	OPENQUERY(STUDDATA97,'SELECT PR.*,SL.Login FROM Parents AS PR 
					LEFT JOIN [Staff Logins] AS SL ON PR.ParentID = SL.ParentID
					WHERE Staff = 1') OP
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {Person}''') AS P
					ON P.PersonID = CAST(OP.ParentID+10000 AS NVARCHAR)
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {Current_YearTerm}''') AS CYT
					ON 2>1
	LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {Staff}''') AS RS
					ON RS.Entity1ID = P.EntityID AND GETDATE() BETWEEN RS.StartDate AND RS.EndDate
					-- Limits this query to people who are not in the system and are either current staff or parents of students
	WHERE OP.[Staff Active Now?] = 1 AND RS.RelationshipName IS NULL

	-- Create 'Married' relationships between parents and staff
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID,WriteType)
	SELECT '{"ShortRelationshipName":"Married"' 
		 + ',"Entity1ID":"' + CAST(P.EntityID AS NVARCHAR) + '"'
		 + ',"Entity2ID":"' + CAST(P2.EntityID AS NVARCHAR) + '"}' AS JSONvalue,
		 P.EntityID AS EntityID,'EntityRelationship' AS WriteType
	--SELECT * 
	FROM 
	-- This filters the list by males to prevent creating two marriage relationships for each couple.
	OPENQUERY(STUDDATA97,'SELECT P1.ParentID AS ParentID,P2.ParentID AS SpouseID FROM Parents AS P1 INNER JOIN Parents AS P2
							ON (P1.ParentID = P2.SpouseID AND P1.SpouseID = P2.ParentID AND P1.ParentID <> P1.SpouseID AND P1.Sex = -1)') MP
	INNER JOIN
	OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL 
					@NOSQL = ''SELECT * FROM {Person}''') AS P
		ON P.PersonID = CAST(MP.ParentID+10000 AS NVARCHAR)
	INNER JOIN
	OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL 
					@NOSQL = ''SELECT * FROM {Person}''') AS P2
		ON P2.PersonID = CAST(MP.SpouseID+10000 AS NVARCHAR)
	LEFT JOIN
	OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL 
					@NOSQL = ''SELECT * FROM {Married}''') AS MR
		ON (MR.Entity1ID = P.EntityID AND MR.Entity2ID = P2.EntityID) OR 
			(MR.Entity2ID = P.EntityID AND MR.Entity1ID = P2.EntityID)
	WHERE MR.Entity2ID IS NULL
			
	---- Remove invalid 'Married' relationships between parents and staff 
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID,WriteType)
	SELECT '{"RequestedAction":"Delete","DeleteOverride":"True"'
		 + ',"ShortRelationshipName":"Married"' 
		 + ',"Entity1ID":"' + CAST(P.EntityID AS NVARCHAR) + '"'
		 + ',"Entity2ID":"' + CAST(P2.EntityID AS NVARCHAR) + '"}' AS JSONvalue,
		 P.EntityID AS EntityID,'EntityRelationship' AS WriteType
	--SELECT * 
	FROM 
	OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL 
					@NOSQL = ''SELECT * FROM {Person}''') AS P
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL 
					@NOSQL = ''SELECT * FROM {Married}''') AS MR
		ON P.EntityID = MR.Entity1ID
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL 
					@NOSQL = ''SELECT * FROM {Person}''') AS P2
		ON P2.EntityID = MR.Entity2ID
	LEFT JOIN
	OPENQUERY(STUDDATA97,'SELECT P1.ParentID AS ParentID,P2.ParentID AS SpouseID FROM Parents AS P1 INNER JOIN Parents AS P2
							ON (P1.ParentID = P2.SpouseID AND P1.SpouseID = P2.ParentID AND P1.ParentID <> P1.SpouseID)') MP
		ON CAST(MP.ParentID+10000 AS NVARCHAR) = P.PersonID AND CAST(MP.SpouseID+10000 AS NVARCHAR) = P2.PersonID
	WHERE MP.ParentID IS NULL

	RETURN 
END